﻿
/****** Object:  User Defined Function dbo.FiscalWeekEnd    Script Date: 10/14/2009 10:39:06 PM ******/

CREATE function FiscalWeekEnd( @FiscalWeek int, @FiscalYear int ) 
returns smalldatetime
as 
begin
declare @FirstDayOfYear int
declare @tmpDate datetime
 
-- Get the first day of the year ( based on Monday rather than Sunday )
set @FirstDayOfYear = dbo.FirstDayOfYear(@FiscalYear) - 1
if @FirstDayOfYear < 1  set @FirstDayOfYear = 7


set @tmpDate = convert( datetime, '1/1/' + cast( @FiscalYear as char ) , 101)

set @tmpDate = dateadd( day, ( ( @FiscalWeek ) * 7 ) - @FirstDayOfYear, @tmpDate )

while datepart( year, @tmpDate ) > @FiscalYear
   set @tmpDate = dateadd( day, -1, @tmpDate )

-- Adjust the date to the last minute of the last day of the fiscal week.
set @tmpDate = dateadd( minute, -1, dateadd( day, 1, @tmpDate ) )

return( @tmpDate )
end

